/****************************************************************************
 *
 * Copyright (C) 2003-2008 Los Alamos National Security, LLC
 *                         Packet Analytics Corporation
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License Version 2 as
 * published by the Free Software Foundation.  You may not use, modify or
 * distribute this program under any other version of the GNU General
 * Public License.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
 *
 ****************************************************************************/
package nfse.gui.server;

import com.google.gwt.user.server.rpc.*;

import nfse.NetFSE;
import nfse.NetFSEUtil;
import nfse.gui.client.alert.*;
import nfse.gui.client.reports.TopValues;
import nfse.gui.query.QueryResult;
import nfse.gui.record.*;

import java.sql.*;
import java.util.*;
import nfse.NetFSERecord;

public class AlertServiceImpl extends RemoteServiceServlet implements AlertService {

    private static Connection connectionResult = null;

    private static Connection connectionMetadata = null;

    private static Connection connectionAlerts = null;

    public int delete(String deleteSQL) {
        try {

            if (ServerUtil.IS_TEST_DRIVE) {
                return 0;// "This feature disabled for the test drive.";
            }

            Statement s1 = createAlertStatement();

            s1.execute("delete from alerts where " + deleteSQL);

            ResultSet rs = s1.executeQuery("select count(*) as a from alerts");
            rs.next();
            int count2 = rs.getInt("a");
            rs.close();

            s1.close();
            return count2;
        } catch (Exception E) {
            E.printStackTrace();
        }
        return 0;
    }

    public TopValues getTopValues(String[] fields, int n, String where) {
        Statement s = null;
        TopValues temp = new TopValues();
        try {

            boolean srcip = false;
            boolean dstip = false;
            boolean srcport = false;
            boolean dstport = false;
            boolean protocol = false;
            boolean message = false;

            Vector tempColNames = new Vector();
            for (int i = 0; i < fields.length; i++) {
                if (fields[i].equals("message")) {
                    message = true;
                    tempColNames.addElement("Message");
                } else if (fields[i].equals("srcip")) {
                    srcip = true;
                    tempColNames.addElement("Src IP");
                } else if (fields[i].equals("dstip")) {
                    dstip = true;
                    tempColNames.addElement("Dst IP");
                } else if (fields[i].equals("srcport")) {
                    srcport = true;
                    tempColNames.addElement("Src Port");
                } else if (fields[i].equals("dstport")) {
                    dstport = true;
                    tempColNames.addElement("Dst Port");
                } else if (fields[i].equals("protocol")) {
                    protocol = true;
                    tempColNames.addElement("Protocol");
                }
            }

            String[] colNames = new String[tempColNames.size()];
            for (int i = 0; i < colNames.length; i++) {
                colNames[i] = (String) tempColNames.elementAt(i);
            }

            s = createAlertStatement();

            String fieldStr = "";
            for (int i = 0; i < fields.length; i++) {
                if (i > 0)
                    fieldStr += ", ";
                fieldStr += fields[i];
            }

            String where2 = "";
            if (where.length() > 0)
                where2 = " where " + where;

            String sql = "";
            ResultSet rs = null;
            try {
                sql = "select " + fieldStr + ", count(*) as a from alerts " + where2 + " group by " + fieldStr
                        + " order by a desc limit " + n;
                System.out.println(sql);
                s = connectionResult.createStatement();
                rs = s.executeQuery(sql);
            } catch (Exception e) {
                e.printStackTrace();
                // rs.close();
                s.close();
                return temp;
            }

            int total = 0;

            Vector values = new Vector();
            Vector colValues = new Vector();
            Vector counts = new Vector();
            while (rs.next()) {
                int dstPort = -1;
                int srcPort = -1;
                String srcIP = "";
                String dstIP = "";
                int proto = -1;
                String msg = "";

                String[] colValueEntry = new String[fields.length];

                String valueStr = "";
                for (int i = 0; i < fields.length; i++) {
                    String fieldVal = rs.getString(fields[i]);
                    colValueEntry[i] = fieldVal;
                    if (i > 0)
                        valueStr += " and ";
                    if (fields[i].equals("srcip") || fields[i].equals("dstip") || fields[i].equals("message"))
                        valueStr += fields[i] + "='" + fieldVal + "' ";
                    else
                        valueStr += fields[i] + "=" + fieldVal + " ";
                    String displayStr = "";

                }
                values.addElement(valueStr);
                colValues.addElement(colValueEntry);
                int count = new Integer(rs.getInt("a"));
                total += count;
                counts.addElement(count);
            }
            rs.close();

            temp.total = NetFSEUtil.formattedInteger(total);

            if (values.size() == 0) {
                s.close();
                return temp;
            }

            // Determine time interval

            sql = " select min(startts) as a, max(startts) as b from alerts " + where2 + " ";
            rs = s.executeQuery(sql);
            rs.next();
            Timestamp minTS = rs.getTimestamp("a");
            Timestamp maxTS = rs.getTimestamp("b");
            temp.startTS = minTS.toString();
            temp.endTS = maxTS.toString();
            rs.close();

            long intervalCount = 50;
            long diff = maxTS.getTime() - minTS.getTime();
            diff = diff / 1000; // convert from millis to seconds

            long val = Math.round(diff / intervalCount);
            if (val == 0)
                val++;

            temp.colNames = colNames;
            temp.stats = new int[values.size()][50];
            temp.highs = new String[values.size()];
            temp.lows = new String[values.size()];
            temp.totals = new String[values.size()];
            temp.values = new String[values.size()];
            temp.colValues = new String[values.size()][colNames.length];
            temp.percents = new String[values.size()];
            for (int i = 0; i < values.size(); i++) {
                int min = -1;
                int max = -1;
                temp.values[i] = (String) values.elementAt(i);
                temp.colValues[i] = (String[]) colValues.elementAt(i);
                // if (field == NetFSE.FIELD_PROTOCOL) {
                // temp.values[i] =
                // NetFSEUtil.getProtocolName(Integer.parseInt(temp.values[i]));
                // }
                temp.totals[i] = NetFSEUtil.formattedInteger((Integer) counts.elementAt(i));
                temp.percents[i] = NetFSEUtil.formattedDouble(((float) (Integer) counts.elementAt(i)) / (float) total
                        * 100.0);
                String where3 = "";
                if (where.length() > 0)
                    where3 = "(" + where + ") and ";

                sql = "select " + fieldStr + ", count(*) as b, trunc(extract(epoch from startts)/" + val
                        + ") as a from alerts where " + where3 + " " + values.elementAt(i) + " group by " + fieldStr
                        + ", a limit " + intervalCount;
                System.out.println(sql);
                int oopsVal = -1;
                rs = s.executeQuery(sql);
                while (rs.next()) {
                    int count = rs.getInt("b");
                    if (count > max) {
                        max = count;
                    }
                    if (min == -1 || count < min) {
                        min = count;
                    }

                    long time = (rs.getLong("a") * val);
                    long idx2 = time - (minTS.getTime() / 1000);
                    int idx = (int) (idx2 / val);
                    if (idx >= intervalCount) {
                        oopsVal = count;
                    } else if (idx < 0) {
                        // no op
                    } else {
                        temp.stats[i][idx] = count;
                    }
                }
                rs.close();
                temp.lows[i] = NetFSEUtil.formattedInteger(min);
                temp.highs[i] = NetFSEUtil.formattedInteger(max);

                if (oopsVal != -1) {
                    for (int j = 1; j < temp.stats[i].length; j++) {
                        temp.stats[i][j - 1] = temp.stats[i][j];
                    }
                    temp.stats[i][(int) intervalCount - 1] = oopsVal;
                }

            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                s.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return temp;
    }

    public AlertServiceImpl() {
        super();

        try {

        } catch (Exception e) {
            e.printStackTrace();
        }

        if (NetFSE.TYPE_CLASS_NAMES == null) {
            try {
                NetFSE.configure(NetFSE.CONFIG_FILE);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public QueryResult get(int n, int offset, String where, String orderBy) {
        String sql = "";
        try {

            Statement s1 = createAlertStatement();

            QueryResult qr = new QueryResult();
            qr.count = -1;
            

            
            String table = "alerts";

            ResultSet rs1 = null;

            int idx = where.toUpperCase().indexOf("WHERE");
            if (idx >= 0)
                where = where.substring(idx + 6);

            sql = "select count(*) as a from alerts ";

            if (where.length() > 0) {
                sql += " where " + where + "";
            }

            rs1 = s1.executeQuery(sql);
            rs1.next();

            qr.count = rs1.getInt("a");

            int count = qr.count;

            if (offset < 0)
                offset = 0;

            int first = offset + 1;
            int last = offset + n;
            if (last > count)
                last = count;

            String record = "";
            String recordHex = "";
            String line = "";

            int sensor = -1;

            sql = "select * from alerts ";
            if (where.length() > 0) {
                sql += " where " + where + " ";
            }
            if (orderBy.length() > 0) {
                sql += " order by " + orderBy;
            }
            sql += " limit " + n + " offset " + offset;

            // System.out.println(sql);

            try {
                rs1 = s1.executeQuery(sql);
            } catch (Exception e) {
                // System.out.println(sql);
                // e.printStackTrace();

                s1.close();

                // c.close();
                return null;
            }

            qr.flexRecordGroup = new FlexRecordGroup();
            qr.alertFlexRecordGroup = new AlertFlexRecordGroup();
            
            Vector records = new Vector();
            Vector records2 = new Vector();
            while (rs1.next()) {
               
                sensor = rs1.getInt("sensorid");
                
                int type = rs1.getInt("typeid");
                qr.flexRecordGroup.typeID = type;
                NetFSERecord rec = NetFSE.getTypeClass(type);
                NetFSERecord tempRec = rec.generate(NetFSEUtil.convertFromHex(rs1.getString("recordhex")), sensor);
                
                FlexRecord temp2 = tempRec.getFlexRecord();
                qr.flexRecordGroup.flexRecordFields = rec.getFlexRecordFields();
                temp2.colNames = qr.flexRecordGroup.getFieldNames();
                
                //System.out.println(temp2);
                
                FlexRecord temp = new FlexRecord();
                temp.values = new String[9];
                int idx2 = -1;
                temp.values[++idx2] = rs1.getString("detectts");
                temp.values[++idx2] = rs1.getString("startts");
                temp.values[++idx2] = rs1.getString("endts");
                temp.values[++idx2] = rs1.getString("srcip");
                int srcPort = rs1.getInt("srcport");
                if (srcPort != -1)
                    temp.values[++idx2] = srcPort + "";
                else
                    temp.values[++idx2] = "";
                temp.values[++idx2] = rs1.getString("dstip");
                int dstPort = rs1.getInt("dstport");
                if (dstPort != -1)
                    temp.values[++idx2] = dstPort + "";
                else
                    temp.values[++idx2] = "";
                int protocol = rs1.getInt("protocol");
                if (protocol != -1)
                    temp.values[++idx2] = NetFSEUtil.getProtocolName(protocol);
                else
                    temp.values[++idx2] = "";

                temp.values[++idx2] = rs1.getString("message");
                // System.out.println(temp.toString());
                records.addElement(temp);
                records2.addElement(temp2);
            }
            rs1.close();
            s1.close();

            FlexRecordField[] fields = new FlexRecordField[9];
            int idx2 = -1;
            fields[++idx2] = new FlexRecordField("Detect Time", "detectts", FlexRecordField.TYPE_STRING,
                    FlexRecordField.SUB_TYPE_TIMESTAMP);
            fields[++idx2] = new FlexRecordField("Start Time", "startts", FlexRecordField.TYPE_STRING,
                    FlexRecordField.SUB_TYPE_TIMESTAMP);
            fields[++idx2] = new FlexRecordField("End Time", "endts", FlexRecordField.TYPE_STRING,
                    FlexRecordField.SUB_TYPE_TIMESTAMP);
            fields[++idx2] = new FlexRecordField("Src IP", "srcip", FlexRecordField.TYPE_STRING,
                    FlexRecordField.SUB_TYPE_IP_ADDR);
            fields[++idx2] = new FlexRecordField("Src Port", "srcport", FlexRecordField.TYPE_INT,
                    FlexRecordField.SUB_TYPE_PORT);
            fields[++idx2] = new FlexRecordField("Dst IP", "dstip", FlexRecordField.TYPE_STRING,
                    FlexRecordField.SUB_TYPE_IP_ADDR);
            fields[++idx2] = new FlexRecordField("Dst Port", "dstport", FlexRecordField.TYPE_INT,
                    FlexRecordField.SUB_TYPE_PORT);
            fields[++idx2] = new FlexRecordField("Protocol", "protocol", FlexRecordField.TYPE_STRING,
                    FlexRecordField.SUB_TYPE_NONE);
            fields[++idx2] = new FlexRecordField("Message", "message", FlexRecordField.TYPE_STRING,
                    FlexRecordField.SUB_TYPE_NONE);

            FlexRecord[] temp = new FlexRecord[records.size()];
            for (int i = 0; i < temp.length; i++)
                temp[i] = (FlexRecord) records.elementAt(i);
            FlexRecord[] temp2 = new FlexRecord[records2.size()];
            for (int i = 0; i < temp2.length; i++)
                temp2[i] = (FlexRecord) records2.elementAt(i);

           
            qr.flexRecordGroup.typeID = -1;
            
            
            qr.alertFlexRecordGroup.typeID = -1;

            qr.alertFlexRecordGroup.alerts = temp;
            qr.alertFlexRecordGroup.flexRecordAlertFields = fields;
            qr.alertFlexRecordGroup.records = temp2;
            qr.qs = null;

            return qr;
        } catch (Exception E) {
            E.printStackTrace();
        }
        return null;
    }

    private String convertFromHex(String line) {
        byte[] bytes = new byte[line.length() / 2];
        for (int i = 0; i < bytes.length; i++)
            bytes[i] = (byte) Integer.parseInt(line.substring(i * 2, i * 2 + 2), 16);
        return new String(bytes);
    }

    private Statement createResultStatement() throws Exception {
        Statement s = null;
        try {
            s = connectionResult.createStatement();
        } catch (Exception e) {
            Class.forName("org.postgresql.Driver");
            String url = "jdbc:postgresql://localhost/" + NetFSE.RESULT_DB_DATABASE;
            Properties props = new Properties();
            props.setProperty("user", NetFSE.RESULT_DB_USER);
            props.setProperty("password", NetFSE.RESULT_DB_PASSWORD);
            props.setProperty("client_encoding", "UTF8");

            connectionResult = DriverManager.getConnection(url, props);

            s = connectionResult.createStatement();
        }
        return s;
    }

    private Statement createAlertStatement() throws Exception {
        Statement s = null;
        try {
            s = connectionResult.createStatement();
        } catch (Exception e) {
            Class.forName("org.postgresql.Driver");
            String url = "jdbc:postgresql://" + NetFSE.ALERT_DB_HOST + "/" + NetFSE.ALERT_DB_DATABASE;
            Properties props = new Properties();
            props.setProperty("user", NetFSE.ALERT_DB_USER);
            props.setProperty("password", NetFSE.ALERT_DB_PASSWORD);
            props.setProperty("client_encoding", "UTF8");

            connectionResult = DriverManager.getConnection(url, props);

            s = connectionResult.createStatement();
        }
        return s;
    }

    private Statement createMetadataStatement() throws Exception {
        Statement s = null;
        try {
            s = connectionMetadata.createStatement();
        } catch (Exception e) {
            Class.forName("org.postgresql.Driver");
            String url = "jdbc:postgresql://" + NetFSE.METADATA_HOST + "/" + NetFSE.METADATA_DATABASE;
            Properties props = new Properties();
            props.setProperty("user", NetFSE.METADATA_USER);
            props.setProperty("password", NetFSE.METADATA_PASSWORD);
            props.setProperty("client_encoding", "UTF8");

            connectionMetadata = DriverManager.getConnection(url, props);

            s = connectionMetadata.createStatement();
        }
        return s;
    }

}
